﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DataModel;

namespace SchoolDataLayer
{
   public class DLStudent
    {
       public int AddStudent(MStudent _student) 
       {
           using (SqlConnection conn = DBConn.GetConnectionString())
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   string proc = "SADB.AddStudent";
                   cmd.CommandText = proc;
                   cmd.CommandType = CommandType.StoredProcedure;
                   cmd.Parameters.AddWithValue("@roll", _student.Roll);
                   cmd.Parameters.AddWithValue("@firstname", _student.FirstName);
                   cmd.Parameters.AddWithValue("@middlename", _student.MiddleName);
                   cmd.Parameters.AddWithValue("@grade", _student.Grade);
                   cmd.Parameters.AddWithValue("@lastname", _student.LastName);
                   cmd.Parameters.AddWithValue("@address", _student.Address);
                   cmd.Parameters.AddWithValue("@pfirstname", _student.Parent.FirstName);
                   cmd.Parameters.AddWithValue("@pmiddlename", _student.Parent.MiddleName);
                   cmd.Parameters.AddWithValue("@plastname", _student.Parent.LastName);
                   cmd.Parameters.AddWithValue("@PPhone", _student.Parent.Phone);
                   cmd.Parameters.AddWithValue("@paddress", _student.Parent.Address);
                   cmd.Parameters.AddWithValue("@hostle", _student.Hostel);
                   cmd.Parameters.AddWithValue("@transport", _student.Transport);
                   cmd.Parameters.AddWithValue("@section", _student.section);
                   cmd.Parameters.AddWithValue("@dob", _student.DOB);
                   cmd.Parameters.AddWithValue("@sphone", _student.Phone);
                   cmd.Parameters.AddWithValue("@mobile", _student.Mobile);
                   cmd.Parameters.AddWithValue("@imageId", _student.photoId);
                   return cmd.ExecuteNonQuery();
               }
           }
       
       }
       public List<MStudent> GetAllStudent()
       {
           List<MStudent> student = new List<MStudent>();
           using (SqlConnection conn = DBConn.GetConnectionString())
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   string proc = "ProcGetAllStudent";
                   cmd.CommandText = proc;
                   cmd.CommandType = CommandType.StoredProcedure;
                   SqlDataReader reader = cmd.ExecuteReader();
                   if (reader != null)
                   {
                       while (reader.Read())
                       {
                           student.Add(
                               new MStudent
                               {
                                   Studentid = (int)reader["Id"],
                                   FirstName = (string)reader["FirstName"],
                                   MiddleName = (string)reader["MiddleName"],
                                   LastName = (string)reader["LastName"],
                                   Address = (string)reader["Address"],
                                   DOB = (DateTime)reader["DOB"],
                                   Roll = (int)reader["Roll"],
                                   section = (string)reader["Section"],
                                   Grade=(int)reader["Grade"]
                               }
                               );
                       }
                  }
                   cmd.Dispose();
                   reader.Dispose();
               }
           }
           return student;
       }

       //getstudent by id
       public MStudent GetStudentById(int id)
       {
           MStudent student = new MStudent();
           StudentParent parent = new StudentParent();
           student.Parent = parent;
           Images img = new Images();
           student.images = img;
           using (SqlConnection conn = DBConn.GetConnectionString())
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   cmd.CommandText = "ProcGetStudentById";
                   cmd.CommandType = CommandType.StoredProcedure;
                   cmd.Parameters.AddWithValue("@id", id);
                   SqlDataReader reader = cmd.ExecuteReader();
                   if (reader != null)
                   {
                       while (reader.Read())
                       {
                           student.FirstName = (string)reader["StudentName"];
                           student.Hostel = (bool)reader["Hostel"];
                           student.Transport = (bool)reader["Transport"];
                           student.Phone = (string)reader["stdPhone"];
                           student.Mobile = (string)reader["stdMobile"];
                           student.Address = (string)reader["StdAddress"];
                           student.Roll = (int)reader["Roll"];
                           student.Grade = (int)reader["Grade"];
                           student.DOB = (DateTime)reader["DOB"];
                           student.section = (string)reader["Section"];
                           student.Parent.FirstName = (string)reader["PrtName"];
                           student.Parent.MiddleName = (string)reader["PrtMName"];
                           student.Parent.LastName = (string)reader["PrtLName"];
                           student.Parent.Address=(string)reader["PrtAddress"];
                           student.Parent.Phone = (string)reader["PrtPhone"];
                           student.images.PhotoPath = (string)reader["img"];
                       }
                   }
                   cmd.Dispose();
                   reader.Dispose();
               }
           }
           return student;
       }
       public List<MStudent> GetStudentListByClassSection(int grade, string section)
       {
           List<MStudent> student = new List<MStudent>();
           using (SqlConnection conn = DBConn.GetConnectionString())
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   cmd.CommandText = "ProcGetStudentByClass";
                   cmd.CommandType = CommandType.StoredProcedure;
                   cmd.Parameters.AddWithValue("@grade", grade);
                   cmd.Parameters.AddWithValue("@section", section);
                   SqlDataReader reader = cmd.ExecuteReader();
                   if(reader!=null)
                   {
                       while (reader.Read())
                       {
                           student.Add(
                               new MStudent
                               {
                                   FirstName = (string)reader["Name"],
                                   Roll = (int)reader["Roll"],
                                   Studentid = (int)reader["Id"]

                               }
                               );
                       }
                }
                   cmd.Dispose();
                   reader.Dispose();

               }
           }
           return student;
       }
       //record student attendence
       public int SaveAttendence(Attend _attendence)
       {
           using (SqlConnection conn = DBConn.GetConnectionString())
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   cmd.CommandText = "ProcAttendence";
                   cmd.CommandType=CommandType.StoredProcedure;
                   cmd.Parameters.AddWithValue("@present", _attendence.Present);
                   cmd.Parameters.AddWithValue("@absent",_attendence.Absent);
                   cmd.Parameters.AddWithValue("@late",_attendence.Late);
                   cmd.Parameters.AddWithValue("@studentid",_attendence.StudentId);
                   cmd.Parameters.AddWithValue("@subjectid", _attendence.SubjectId);
                   cmd.Parameters.AddWithValue("@date", DateTime.Now);
                  return cmd.ExecuteNonQuery();
                   
               }
           }
       }

       public int DeleteStudentbyId(int studentId)
       {
           using (SqlConnection conn = DBConn.GetConnectionString())
           {
               using (SqlCommand cmd = conn.CreateCommand())
               {
                   cmd.CommandText = "ProcDeleteStudent";
                   cmd.CommandType = CommandType.StoredProcedure;
                   cmd.Parameters.AddWithValue("@studentId", studentId);
                   return cmd.ExecuteNonQuery();
               }
           }
       }

    }
 }
